package com.dckeji.shiplock.webapp.dao.impl;

import com.dckeji.shiplock.framework.pojo.LockInfo;
import com.dckeji.shiplock.framework.pojo.ReViewInfo;
import com.dckeji.shiplock.framework.pojo.ShipPayInfo;
import com.dckeji.shiplock.framework.pojo.UnLockInfo;
import com.dckeji.shiplock.framework.util.DateTimeUtil;
import com.dckeji.shiplock.webapp.dao.BaseDao;
import com.dckeji.shiplock.webapp.dao.IManagerDao;
import com.dckeji.shiplock.webapp.pojo.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.ExecutionException;

/**
 * Created with IntelliJ IDEA.
 * User: TianWei
 * Date: 2017/8/9
 * Time: 17:38
 */
@Repository
@Transactional(rollbackFor = Exception.class)
public class ManagerDaoImpl extends BaseDao implements IManagerDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public Manager queryManagerInfo(final Manager manager) {

        String sql = "SELECT * FROM manager WHERE managerUserName =? AND managerPwd = ? LIMIT 1";


        List<Manager> query1 = jdbcTemplate.query(sql, new Object[]{manager.getManagerUserName(), manager.getManagerPwd()}, new RowMapper<Manager>() {
            @Override
            public Manager mapRow(ResultSet resultSet, int i) throws SQLException {
                Manager manager1 = new Manager();
                manager1.setManagerId(resultSet.getInt("managerId"));
                manager1.setManagerPwd(resultSet.getString("managerPwd"));
                manager1.setManagerUserName(resultSet.getString("managerUserName"));
                return manager1;
            }
        });
        if (null != query1 && query1.size() > 0) {
            return query1.get(0);
        }

        return null;
    }

    @Override
    public int managerLogin(Manager manager, String addrName) {
        String sql = "INSERT INTO managerlogin(managerId,managerLoginTime,managerLoginIp,managerLoginIpName) VALUES(?,?,?,?)";

        return jdbcTemplate.update(sql, new Object[]{manager.getManagerId(), DateTimeUtil.getSystemTime(), manager.getManagerLoginIp(), addrName});
    }

    @Override
    public List<Login> queryManagerLoginHistory() {
        StringBuffer sql = new StringBuffer();
        sql.append("select managerLoginId,m.managerUserName,managerLoginTime,managerLoginIp,managerLoginIpName from managerlogin ml")
                .append(" INNER JOIN manager m on m.managerId=ml.managerId")
                .append(" ORDER BY managerLoginTime desc  limit 1,12");
        List<Login> logins = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<Login>(Login.class));
        return logins;
    }

    @Override
    public SystemMessage querySystemMessage() {
        String paySql = "select isExamine,count(1) as count from pay group by isExamine";
        final SystemMessage systemMessage = new SystemMessage();
        jdbcTemplate.query(paySql, new RowMapper<SystemMessage>() {
            @Override
            public SystemMessage mapRow(ResultSet resultSet, int i) throws SQLException {
                int isExamine = resultSet.getInt("isExamine");
                int count = resultSet.getInt("count");
                switch (isExamine) {
                    case 0:
                        systemMessage.setUntreatedCount(systemMessage.getUntreatedCount() + count);
                        break;
                    case 1:
                        systemMessage.setTotalExamineCount(systemMessage.getTotalExamineCount() + count);
                        break;
                    case 2:
                        systemMessage.setTotalRejectCount(systemMessage.getTotalRejectCount() + count);
                        break;
                }
                return systemMessage;
            }
        });
        String timeoutapply = "select isExamine,count(1) as count from timeoutapply group by isExamine";
        jdbcTemplate.query(timeoutapply, new RowMapper<SystemMessage>() {
            @Override
            public SystemMessage mapRow(ResultSet resultSet, int i) throws SQLException {
                int isExamine = resultSet.getInt("isExamine");
                int count = resultSet.getInt("count");
                switch (isExamine) {
                    case 0:
                        systemMessage.setUntreatedCount(systemMessage.getUntreatedCount() + count);
                        break;
                    case 1:
                        systemMessage.setTotalExamineCount(systemMessage.getTotalExamineCount() + count);
                        break;
                    case 2:
                        systemMessage.setTotalRejectCount(systemMessage.getTotalRejectCount() + count);
                        break;
                }
                return systemMessage;
            }
        });

        String decla = "select isExamine,count(1) as count from decla group by isExamine";
        jdbcTemplate.query(decla, new RowMapper<SystemMessage>() {
            @Override
            public SystemMessage mapRow(ResultSet resultSet, int i) throws SQLException {
                int isExamine = resultSet.getInt("isExamine");
                int count = resultSet.getInt("count");
                switch (isExamine) {
                    case 0:
                        systemMessage.setUntreatedCount(systemMessage.getUntreatedCount() + count);
                        break;
                    case 1:
                        systemMessage.setTotalExamineCount(systemMessage.getTotalExamineCount() + count);
                        break;
                    case 2:
                        systemMessage.setTotalRejectCount(systemMessage.getTotalRejectCount() + count);
                        break;
                }
                return systemMessage;
            }
        });


        return systemMessage;
    }

    @Override
    public List<Evaluate> queryNewEvaluates() {
        String sql = "select evaluateId,shipNo,postName,`desc`,commitTime,unLockTime from evaluate  e INNER JOIN post p on p.postId=e.postId where optId=2";

        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Evaluate>(Evaluate.class));
    }

    @Override
    public int examinPay(Examine examin) {
        String sql = "update pay set isExamine=? ,examineTime=?,`desc`=? where payId=?";

        return jdbcTemplate.update(sql, new Object[]
                {examin.getOptType(), examin.getExamineTime(), examin.getRejectDesc(), examin.getExamineId()});
    }

    @Override
    public int examinDecla(Examine examin) {
        String sql = "update decla set isExamine=? ,examineTime=?,`desc`=? where declareId=?";
        return jdbcTemplate.update(sql, new Object[]
                {examin.getOptType(), examin.getExamineTime(), examin.getRejectDesc(), examin.getExamineId()});
    }

    @Override
    public int examinTimeoutApply(Examine examin) {

        String sql = "update timeoutapply set isExamine=? ,examineTime=?,`desc`=? where timeOutApplyId=?";
        return jdbcTemplate.update(sql, new Object[]
                {
                        examin.getOptType(), examin.getExamineTime(), examin.getRejectDesc(), examin.getExamineId()
                });
    }

    @Override
    public int batchUpDateLockInfo(final List<Object> objects) {
        StringBuffer sql = new StringBuffer();
        sql
                .append("INSERT INTO lockinfo ")
                .append("(course,shipLockNo,lockNo,dispatchTime,unLockConfirmTime,lockStatus,lockType,shipsCount,shipCount,bargeCount,lockMoney,totalHeavy)")
                .append(" VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
        int[] ints = jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                LockInfo lockInfo = (LockInfo) objects.get(i);
                preparedStatement.setString(1, lockInfo.getCourse());
                preparedStatement.setString(2, lockInfo.getShipLockNo());
                preparedStatement.setString(3, lockInfo.getLockNo());
                preparedStatement.setString(4, lockInfo.getDispatchTime());
                preparedStatement.setString(5, lockInfo.getUnLockConfirmTime());
                preparedStatement.setString(6, lockInfo.getLockStatus());
                preparedStatement.setString(7, lockInfo.getLockType());
                preparedStatement.setString(8, lockInfo.getShipsCount());
                preparedStatement.setString(9, lockInfo.getShipCount());
                preparedStatement.setString(10, lockInfo.getBargeCount());
                preparedStatement.setString(11, lockInfo.getLockMoney());
                preparedStatement.setString(12, lockInfo.getTotalHeavy());
            }

            @Override
            public int getBatchSize() {
                return objects.size();
            }
        });
        int row = validaBatch(ints);
        if (row == objects.size()) {
            return objects.size();
        } else {
            return 0;
        }
    }

    @Override
    public int batchUpDateUnLockInfo(final List<Object> objects) {

        String sql = "INSERT INTO unlockinfo(shipNo,course,`begin`,`end`,chargingBase,scheduler,dispatchTime,unLockConfirm,unLockConfirmTime) VALUES(?,?,?,?,?,?,?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                UnLockInfo unLockInfo = (UnLockInfo) objects.get(i);
                preparedStatement.setString(1, unLockInfo.getShipNo());
                preparedStatement.setString(2, unLockInfo.getCourse());
                preparedStatement.setString(3, unLockInfo.getBegin());
                preparedStatement.setString(4, unLockInfo.getEnd());
                preparedStatement.setString(5, unLockInfo.getChargingBase());
                preparedStatement.setString(6, unLockInfo.getScheduler());
                preparedStatement.setString(7, unLockInfo.getDispatchTime());
                preparedStatement.setString(8, unLockInfo.getUnLockConfirm());
                preparedStatement.setString(9, unLockInfo.getUnLockConfirmTime());
            }

            @Override
            public int getBatchSize() {
                return objects.size();
            }
        });
        int row = validaBatch(ints);
        if (row == objects.size()) {
            return 1;
        }
        return 0;
    }

    @Override
    public int batchUpDateReViewInfo(final List<Object> objects) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO review ")
                .append("(shipName,shipType,chargingBase,preTon,reViewCompany,reViewTime,reViewer,`status`,examineTime,auditor)")
                .append("VALUES(?,?,?,?,?,?,?,?,?)");

        int[] ints = jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                ReViewInfo reViewInfo = (ReViewInfo) objects.get(i);
                preparedStatement.setString(1, reViewInfo.getShipName());
                preparedStatement.setString(2, reViewInfo.getShipType());
                preparedStatement.setString(3, reViewInfo.getChargingBase());
                preparedStatement.setString(4, reViewInfo.getPreTon());
                preparedStatement.setString(5, reViewInfo.getReViewCompany());
                preparedStatement.setString(6, reViewInfo.getReViewTime());
                preparedStatement.setString(7, reViewInfo.getReViewer());
                preparedStatement.setString(8, reViewInfo.getStatus());
                preparedStatement.setString(9, reViewInfo.getExamineTime());
                preparedStatement.setString(10, reViewInfo.getAuditor());
            }

            @Override
            public int getBatchSize() {
                return objects.size();
            }
        });
        int row = validaBatch(ints);
        if (row == objects.size()) {
            return 1;
        }
        return 0;
    }

    @Override
    public int batchUpshipPayInfo(final List<Object> objects) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO shippayinfo")
                .append(" (shipNo,shipName,billNo,shipType,payMoney,chargingBase,`status`,registTime,registrant,payTime,payer) ")
                .append("VALUES(?,?,?,?,?,?,?,?,?,?,?)");
        int[] ints = jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                ShipPayInfo shipPayInfo = (ShipPayInfo) objects.get(i);
                preparedStatement.setString(1, shipPayInfo.getShipNo());
                preparedStatement.setString(2, shipPayInfo.getShipName());
                preparedStatement.setString(3, shipPayInfo.getBillNo());
                preparedStatement.setString(4, shipPayInfo.getShipType());
                preparedStatement.setString(5, shipPayInfo.getPayMoney());
                preparedStatement.setString(6, shipPayInfo.getChargingBase());
                preparedStatement.setString(7, shipPayInfo.getStatus());
                preparedStatement.setString(8, shipPayInfo.getRegistTime());
                preparedStatement.setString(9, shipPayInfo.getRegistrant());
                preparedStatement.setString(10, shipPayInfo.getPayTime());

                preparedStatement.setString(11, shipPayInfo.getPayer());

            }

            @Override
            public int getBatchSize() {
                return objects.size();
            }
        });
        int row = validaBatch(ints);
        if (row == objects.size()) {
            return 1;
        }
        return 0;
    }

    @Override
    public int batchUpRegistInfo(List<Object> objects) {




        return 0;
    }

    @Override
    public List<Route> queryRouteByShipNo(String shipNo) {
        String sql = "select * from route  where shipNo=?";

        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Route>(Route.class), new Object[]{shipNo});
    }


}
